﻿using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace WeatherComm
{
    public class CityInfo2
    {
        public int id { get; set; }
        public int pid { get; set; }

        [DisplayName("城市代码")]
        public string city_code { get; set; }
        [DisplayName("城市名称")]
        public string city_name { get; set; }
        public string post_code { get; set; }
        public string area_code { get; set; }
        public string ctime { get; set; }
        public bool Enable { get; set; }

        public static List<CityInfo2> GetFromFile(string fname)
        {
            try
            {
                var str = FileHelper.GetAllText(fname);
                var ls = JsonConvert.DeserializeObject<List<CityInfo2>>(str);
                return ls;
            }
            catch (Exception e)
            {
                return null;
            }
        }
        public static bool Write2Db(List<CityInfo2> values,bool useEf=true)
        {
            if (useEf)
            {
                var ef = new EfHelper<WeatherDataDb>();
                foreach (var r in values)
                {
                    var a = new City
                    {
                        CityKey = r.city_code,
                        CityName = r.city_name,
                        Enable = r.Enable
                    };
                    ef.Add(a);
                }

                return true;
            }


            var connectString = ConfigurationManager.AppSettings["Connection"];
            try
            {
                using (var conn = new SqlConnection(connectString))
                {
                    conn.Open();

                    foreach (var r in values)
                    {
                        if (string.IsNullOrEmpty(r.city_code))
                        {
                            continue;
                        }
                        var cmd = new SqlCommand($@"INSERT INTO [dbo].[City] ([City],[CityKey]) VALUES ('{r.city_name}','{r.city_code}')", conn);
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                    }
                    conn.Close();
                }
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }

        }

        public static List<CityInfo2> GetFromDb(bool useEf =true)
        {
            try
            {
                if (useEf)
                {
                    var ef = new EfHelper<WeatherDataDb>();
                    var cityList = ef.GetList<City>().ToList();
                    var ls = new List<CityInfo2>();
                    foreach (var r in cityList)
                    {
                        var a = new CityInfo2
                        {
                            city_code = r.CityKey,
                            city_name = r.CityName,
                            id = r.ID,
                            Enable = r?.Enable ?? false
                        };
                        ls.Add(a);
                    }
                    return ls;
                }
                var connectString = ConfigurationManager.AppSettings["Connection"];
                var sql = $@"SELECT  * FROM [dbo].[City]";

                using (var conn = new SqlConnection(connectString))
                {
                    conn.Open();
                    var adapter = new SqlDataAdapter(sql, conn);
                    var db = new DataTable();
                    adapter.Fill(db);

                    var ls = db.Rows.Cast<DataRow>().Select(
                        r => new CityInfo2
                        {
                            id = (int)r["ID"], 
                            city_code = r["CityKey"].ToString(),
                            city_name = r["City"].ToString(), 
                            Enable = r["Enable"].ToString() == "True"
                        }).Where(a => a.Enable).ToList();

                    db.Dispose();
                    conn.Close();
                    adapter.Dispose();

                    return ls;
                }
            }
            catch (Exception e)
            {
                return null;
            }
        }
    }

}